﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Collections;
using System.Drawing;

namespace ufolder
{
    public class Label
    {
        private int _id;
        private string _label;

        private Label[] _tags = null;

        private SQLiteConnection c;

        public int Id
        {
            get
            {
                return this._id;
            }
        }

        public Label(string l, SQLiteConnection c)
        {
            //Chercher voir si le tag existe
            this.c = c;
            
            System.Diagnostics.Debug.Assert(Label.CheckValidTag(l), "Tag invalide", "Valeur du tag: " + l);

            string sql = "SELECT id FROM tags WHERE label='" + l.ToLower() + "'";
            SQLiteCommand command = new SQLiteCommand(sql, c);
            command.Transaction = c.BeginTransaction();
            try
            {
                object o = command.ExecuteScalar();

                if (o != null) // Le tag existe déjà
                {
                    this._id = int.Parse(o.ToString());
                    this._label = l.ToLower();
                }
                else
                {
                    sql = "INSERT INTO tags (label) VALUES ('" + l.ToLower() + "');";
                    command.CommandText = sql;
                    int id = -1;
                    command.ExecuteNonQuery();
                    command.CommandText = "SELECT last_insert_rowid()";
                    id = int.Parse(command.ExecuteScalar().ToString());
                    this._id = id;
                    this._label = l.ToLower();
                }
                command.Transaction.Commit();
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
        }

        /// <summary>
        /// Donne la couleur à partir d'un label
        /// </summary>
        /// <param name="l">Label</param>
        /// <param name="c">Connection SQLite ouverte</param>
        /// <returns></returns>
        public static Color GetColorFromLabel(string l, SQLiteConnection c)
        {
            if (!Label.CheckValidTag(l))
                throw new Exception("Label invalide");

            SQLiteCommand command = new SQLiteCommand(c);
            //command.Transaction = c.BeginTransaction();
            command.CommandText = "SELECT id FROM tags WHERE label='" + l.ToLower() + "'";
            int id = -1;

            try
            {
                object o = command.ExecuteScalar();
               
                if (o != null) // Le tag existe déjà
                   id = int.Parse(o.ToString());
                else
                   throw new Exception("Le tag n'existe pas");

                return (Label.Colors[id % Label.Colors.Length]);
            }
            catch (SQLiteException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                throw new Exception("Erreur de récupération de couleur");
            }
        }

        public void AddLabel(Label l, SQLiteConnection c)
        {
            Edge.Create(c, l.Id, this._id);
        }

        public void RemoveLabel(Label l, SQLiteConnection c)
        {
            Edge.Delete(c, l.Id, this._id);
        }


        public static bool CheckValidTag(string s)
        {
            if (s.Length != 0)
                if (s[0] == '-') return false;

            for (int i = 0; i < s.Length; i++)
            {
                //	digit = '0' .. '9'.
                //alphaNum = 'a' .. 'z' + digit + '/' + '@' + '+' + '.' + '#' + 'é' + 'è' + 'à' + 'ç' + '=' + '_'.
                if (s[i] != 'a' &&
                    s[i] != 'b' &&
                    s[i] != 'c' &&
                    s[i] != 'd' &&
                    s[i] != 'e' &&
                    s[i] != 'f' &&
                    s[i] != 'g' &&
                    s[i] != 'h' &&
                    s[i] != 'i' &&
                    s[i] != 'j' &&
                    s[i] != 'k' &&
                    s[i] != 'l' &&
                    s[i] != 'm' &&
                    s[i] != 'n' &&
                    s[i] != 'o' &&
                    s[i] != 'p' &&
                    s[i] != 'q' &&
                    s[i] != 'r' &&
                    s[i] != 's' &&
                    s[i] != 't' &&
                    s[i] != 'u' &&
                    s[i] != 'v' &&
                    s[i] != 'w' &&
                    s[i] != 'x' &&
                    s[i] != 'y' &&
                    s[i] != 'z' &&
                    s[i] != '0' &&
                    s[i] != '1' &&
                    s[i] != '2' &&
                    s[i] != '3' &&
                    s[i] != '4' &&
                    s[i] != '5' &&
                    s[i] != '6' &&
                    s[i] != '7' &&
                    s[i] != '8' &&
                    s[i] != '9' &&
                    s[i] != '/' &&
                    s[i] != '\\' &&
                    s[i] != '@' &&
                    s[i] != '+' &&
                    s[i] != '#' &&
                    s[i] != 'é' &&
                    s[i] != 'è' &&
                    s[i] != 'à' &&
                    s[i] != 'ç' &&
                    s[i] != '=' &&
                    s[i] != '_' && s[i] != '-')
                    return false;
            }
            return true;

        }

        public Label(int id, string label)
        {
            this._id = id; this._label = label;
        }

        public Label(int id, string label, Label[] l) : this(id, label)
        {
            this._tags = (l!=null && l.Length!=0)? l : null;
        }

        public string Tag
        {
            get
            {
                return this._label;
            }
        }

        public override string ToString()
        {
            return this._label;
        }

        #region Colors
        public static readonly Color[] Colors = {
      Color.FromArgb(223,182,246),
      Color.FromArgb(184,194,249),
      Color.FromArgb(255,200,188),
      Color.FromArgb(255,222,188),
      Color.FromArgb(255,239,188),
      Color.FromArgb(254,255,188),
      Color.FromArgb(224,255,188),
      Color.FromArgb(188,230,255),
      Color.FromArgb(255,188,219),
      Color.FromArgb(170,255,213),
      Color.FromArgb(116,136,242),
      Color.FromArgb(255,146,122),
      Color.FromArgb(255,189,122),
      Color.FromArgb(255,222,122),
      Color.FromArgb(252,255,122),
      Color.FromArgb(193,255,122),
      Color.FromArgb(122,205,255),
      Color.FromArgb(255,122,184),
      Color.FromArgb(193,113,237),
      Color.FromArgb(85,255,171),
      Color.FromArgb(51,80,236),
      Color.FromArgb(255,91,55),
      Color.FromArgb(255,156,55),
      Color.FromArgb(255,206,55),
      Color.FromArgb(251,255,55),
      Color.FromArgb(162,255,55),
      Color.FromArgb(55,180,255),
      Color.FromArgb(255,55,148),
      Color.FromArgb(165,49,228),
      Color.FromArgb(0,255,129)
};
#endregion

        public Label[] LinkedTags
        {
            get
            {
                return this._tags;
            }
        }

        public Color TagColor
        {
            get
            {
                return Colors[this._id % Colors.Length];
            }
        }

        public static Label[] FindAll(SQLiteConnection c)
        {
            ArrayList al = new ArrayList();
            string sql = "SELECT * FROM tags";
            
            SQLiteCommand command = new SQLiteCommand(sql, c);
            SQLiteDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
                al.Add(new Label(int.Parse(dr["id"].ToString()), dr["label"].ToString()));
            } 
            
            dr.Close();
            dr.Dispose();

            return (Label[]) (al.ToArray(typeof (Label)));
        }

        public static Label[] FinAllAndLinked(SQLiteConnection c)
        {
            ArrayList al = new ArrayList();
            ArrayList alSub = new ArrayList();
            string sql = "SELECT * FROM tags ORDER BY label";
            SQLiteCommand command = new SQLiteCommand(sql, c);
            //command.Transaction = c.BeginTransaction();
            SQLiteCommand command2 = null;

            SQLiteDataReader dr = command.ExecuteReader();
            SQLiteDataReader dr2 = null;
            string l = null;
            while (dr.Read())
            {
                command2 = new SQLiteCommand(sql, c);
                command2.Transaction = command.Transaction;

                l = dr["label"].ToString();
                sql = "select id, label from tags where label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '" + l + "')";
                command2.CommandText = sql;
                dr2 = command2.ExecuteReader();
                alSub.Clear();

                while (dr2.Read())
                {
                    alSub.Add(new Label(int.Parse(dr2["id"].ToString()), dr2["label"].ToString()));
                }
                al.Add(new Label(int.Parse(dr["id"].ToString()), dr["label"].ToString(), (Label[])alSub.ToArray(typeof (Label))));
            }
            dr.Dispose();

            return (Label[])al.ToArray(typeof(Label));
        }

        public static Label[] FindAllLinkedTags(SQLiteConnection c, Label l)
        {
            return Label.FindAllLinkedTags(c, l.Tag);
        }

        /// <summary>
        /// Trouve tous les tags liés à un tag
        /// </summary>
        /// <param name="c">Connection SQLite ouverte</param>
        /// <param name="l">Valeur du tag à chercher</param>
        /// <returns>Un tableau de Tags</returns>
        public static Label[] FindAllLinkedTags(SQLiteConnection c, string l)
        {
            string sql = "select id, label from tags where label IN (SELECT tag_start.label FROM edges INNER JOIN tags tag_start ON (edges.startvertex = tag_start.id) INNER JOIN tags tag_end ON (edges.endvertex = tag_end.id) WHERE tag_end.label = '"+l+"' UNION SELECT '"+l+"' as label)";
            ArrayList al = new ArrayList();
            SQLiteCommand command = new SQLiteCommand(sql, c);
            //command.Transaction = c.BeginTransaction(); 
            SQLiteDataReader dr = command.ExecuteReader();
            while(dr.Read())
            {
                al.Add(new Label(int.Parse(dr["id"].ToString()), dr["label"].ToString()));
            } 

            dr.Close();

            return (Label[])(al.ToArray(typeof(Label)));
        }

        public static Label Create(SQLiteConnection c, string label)
        {
            string sql = "INSERT INTO tags (label) VALUES ('" + label.ToLower() + "');";
            SQLiteCommand command = new SQLiteCommand(sql, c);

            int id = -1;
            try
            {
                command.Transaction = c.BeginTransaction();
                command.ExecuteNonQuery();
                command.CommandText = "SELECT last_insert_rowid()";
                id = int.Parse(command.ExecuteScalar().ToString());
                command.Transaction.Commit();

                return new Label(id, label);
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                return null;
            }
        }


        public void Rename(string s)
        {
            if (Label.CheckValidTag(s) && this._label!=s)
            {

                SQLiteCommand command = new SQLiteCommand(c);
                SQLiteCommand command2 = new SQLiteCommand(c);
                SQLiteCommand command3 = new SQLiteCommand(c); 
                SQLiteDataReader dr = null;

                try
                {
                    command.Transaction = c.BeginTransaction();

                    command.CommandText = "SELECT items_c.rowid, items_c.tags FROM items_c INNER JOIN items_tags ON (items_c.rowid = items_tags.item_id) WHERE items_tags.tag_id =" + this._id;
                    dr = command.ExecuteReader();

                    command2.Transaction = command.Transaction;

                    while (dr.Read())
                    {
                        string rowid = dr["rowid"].ToString();
                        string tags = dr["tags"].ToString();

                        tags = tags.Replace(this._label, s);

                        command2.CommandText = "UPDATE items_c SET tags='" + tags + "' WHERE rowid=" + rowid;
                        command2.ExecuteNonQuery();
                    }
                    dr.Dispose();

                    command.CommandText = "SELECT id FROM tags WHERE label='" + s + "'";

                    object o = command.ExecuteScalar();
                    int id = -1;

                    
                    command3.Transaction = command.Transaction;

                    if (o != null) // Le tag existe déjà
                    {
                        id = int.Parse(o.ToString());

                        command3.CommandText = "UPDATE items_tags SET tag_id=" + id + " WHERE tag_id = " + this._id + " AND item_id NOT IN (SELECT item_id FROM items_tags WHERE tag_id=" + id +")";
                        command3.ExecuteNonQuery();

                        command3.CommandText = "DELETE FROM items_tags WHERE tag_id = " + this._id;
                        command3.ExecuteNonQuery();

                        command3.CommandText = "DELETE FROM tags WHERE id = " + this._id;
                        command3.ExecuteNonQuery();
                    }
                    else
                    {
                        command3.CommandText = "UPDATE tags SET label = '" + s + "' WHERE label = '" + this._label + "'";
                        command3.ExecuteNonQuery();
                        id = this._id;
                    }

                    command.Transaction.Commit();

                    this._id = id;
                    this._label = s;
                }
                catch (SQLiteException ex)
                {
                    if (dr != null) dr.Dispose();
                    command.Transaction.Rollback();
                    throw new Exception("Erreur SQL", ex);

                    //System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                }
				finally
				{
					if(command != null) command.Dispose();
					if(command2 != null) command2.Dispose();
                    if (command3 != null) command3.Dispose();
				}
            }
            else
            {
                throw new Exception("Tag invalide");
            }
        }

        public void Delete(SQLiteConnection c)
        {
            if (c.State != System.Data.ConnectionState.Open)
                c.Open();

            SQLiteCommand command = new SQLiteCommand(c);
            SQLiteCommand command2 = null;
            SQLiteDataReader dr = null;

            Random rnd = new Random((int)DateTime.Now.Ticks);
            string suffix = rnd.Next().ToString();
            try
            {
                command.Transaction = c.BeginTransaction();

                command.CommandText = "DELETE FROM items_tags WHERE tag_id = " + this._id;
                command.ExecuteNonQuery();

                command.CommandText = "SELECT startvertex FROM edges WHERE hops = 0 AND endvertex = " + this._id + ";";

                dr = command.ExecuteReader();

                while (dr.Read())
                {
                    
                    int startvertex = int.Parse(dr["startvertex"].ToString());
                    int endvertex = this._id;

                    //-----------------
                    command2 = new SQLiteCommand("SELECT id FROM edges WHERE startvertex = " + startvertex + " AND endvertex = " + endvertex + " AND hops = 0 ", c);

                    object ido = command2.ExecuteScalar().ToString();
                    int id;

                    if (ido == null) throw new SQLiteException("Edge direct inexistant");
                    else id = int.Parse(ido.ToString());

                    command2.CommandText = "CREATE TEMP TABLE IF NOT EXISTS purgelist (id int); INSERT INTO purgelist SELECT id FROM edges WHERE directedgeid = " + id + ";";
                    command2.ExecuteNonQuery();

                    command2.CommandText = "INSERT INTO purgelist SELECT id FROM edges WHERE Hops > 0  AND ( entryedgeid IN ( SELECT id FROM purgelist ) OR exitedgeid IN ( SELECT id FROM purgelist ) ) AND id NOT IN (SELECT Id FROM purgelist )";

                    int r = 0;
                    do
                    {
                        r = command2.ExecuteNonQuery();
                    } while (r != 0);

                    command2.CommandText = "DELETE FROM edges WHERE id IN ( SELECT id FROM purgelist);";
                    command2.ExecuteNonQuery();

                    command2.CommandText = "DELETE FROM purgelist";
                    command2.ExecuteNonQuery();
                    //-----------------
                }
                dr.Dispose();


                command.CommandText = "SELECT endvertex FROM edges WHERE hops = 0 AND startvertex = " + this._id + ";";
                dr = command.ExecuteReader();

                while (dr.Read())
                {
                    int startvertex = this._id;
                    int endvertex = int.Parse(dr["endvertex"].ToString());

                    //-----------------
                    command2 = new SQLiteCommand("SELECT id FROM edges WHERE startvertex = " + startvertex + " AND endvertex = " + endvertex + " AND hops = 0 ", c);

                    object ido = command2.ExecuteScalar().ToString();
                    int id;

                    if (ido == null) throw new SQLiteException("Edge direct inexistant");
                    else id = int.Parse(ido.ToString());

                    command2.CommandText = "CREATE TEMP TABLE IF NOT EXISTS purgelist (id int); INSERT INTO purgelist SELECT id FROM edges WHERE directedgeid = " + id + ";";
                    command2.ExecuteNonQuery();

                    command2.CommandText = "INSERT INTO purgelist SELECT id FROM edges WHERE Hops > 0  AND ( entryedgeid IN ( SELECT id FROM purgelist ) OR exitedgeid IN ( SELECT id FROM purgelist ) ) AND id NOT IN (SELECT Id FROM purgelist )";

                    int r = 0;
                    do
                    {
                        r = command2.ExecuteNonQuery();
                    } while (r != 0);

                    command2.CommandText = "DELETE FROM edges WHERE id IN ( SELECT id FROM purgelist);";
                    command2.ExecuteNonQuery();

                    command2.CommandText = "DELETE FROM purgelist";
                    command2.ExecuteNonQuery();
                    //-----------------                   
                }
                dr.Dispose();

                command.CommandText = "DELETE FROM tags WHERE id=" + this._id + ";";
                command.ExecuteNonQuery();

                command.CommandText = "DROP TABLE IF EXISTS purgelist";
                command.ExecuteNonQuery();
               
                command.Dispose();
                if (command2 != null) command2.Dispose();

                command.Transaction.Commit();
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                if (dr != null) dr.Dispose();
                
            }
        }

    }
}